---
title: "02-PostgreSQL 数据类型与表设计"
created: 2026-08-31
tags:
  - 项目筑基
---

# PostgreSQL 数据类型与表设计

> PostgreSQL 系统讲解第二篇：类型系统是 PG 最大的优势之一——本篇过一遍"值得用的类型"和表设计时的关键选择。

## 值得优先掌握的类型

| 类型 | 用途 | 备忘 |
| --- | --- | --- |
| `numeric(p,s)` | **精确**小数 | 金额一律用它；`float`/`double` 有浮点误差 |
| `bigint` + IDENTITY | 主键 | 见下节自增方案 |
| `timestamptz` | 带时区时间戳 | **存 UTC 内部表示，显示按会话时区**——优先用它而不是 timestamp |
| `uuid` | 原生 UUID 类型 | 分布式 id；默认无内置生成函数，用 `gen_random_uuid()`（pgcrypto/13+ 内置） |
| `text` | 不限长字符串 | PG 里 varchar(n) 与 text 性能相同，长度约束用 CHECK 就行——"都建 text 需要时再限"是常见流派 |
| `text[]` | **数组** | 标签、多值字段一行搞定 |
| `jsonb` | 二进制 JSON | 可索引、可查询；`json` 只存原文（慢），**默认用 jsonb** |
| `bytea` | 二进制大对象 | 大文件仍建议对象存储 + URL |

## JSONB：PG 的招牌

```sql
CREATE TABLE events (
    id     bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    data   jsonb NOT NULL
);

INSERT INTO events (data) VALUES ('{"type": "click", "page": "/home", "meta": {"src": "app"}}');

-- 查询操作符
SELECT data->>'type' FROM events;              -- ->> 取文本；-> 取 jsonb
SELECT data->'meta'->>'src' FROM events;
SELECT * FROM events WHERE data @> '{"type": "click"}';   -- @> 包含，可走 GIN 索引
SELECT * FROM events WHERE data ? 'page';                 -- 是否存在顶层 key
```

- **jsonb vs json**：jsonb 解析后存储（支持索引、去重键、次序不定），json 存原文（只能整体读）。**几乎总是选 jsonb**
- **GIN 索引**让 jsonb 查询起飞：`CREATE INDEX ON events USING GIN (data);`
- 定位：**schema 频繁变化的属性数据**用 jsonb；核心业务字段老老实实建列（可约束、可统计、类型安全）

## 数组类型

```sql
SELECT * FROM users WHERE 'python' = ANY(tags);   -- 包含元素
SELECT * FROM users WHERE tags @> ARRAY['python', 'go'];   -- 同时包含
UPDATE users SET tags = tags || '{rust}' WHERE id = 1;    -- 追加元素
CREATE INDEX ON users USING GIN (tags);                   -- 数组查询走 GIN
```

我的记忆钩子：**数组解决"多值属性"，jsonb 解决"半结构化"**——两者一起上 GIN 索引，是 PG 对 MySQL 最大的一波"原生优势"。

## 自增三方案：SERIAL vs IDENTITY vs UUID

```sql
id serial PRIMARY KEY                                -- 老写法：int + 默认序列
id integer GENERATED BY DEFAULT AS IDENTITY          -- SQL 标准，推荐
id uuid DEFAULT gen_random_uuid()                    -- 分布式场景
```

- IDENTITY 优于 SERIAL：权限更严格（防止手滑覆盖序列）、可 `GENERATED ALWAYS` 锁死手工插值
- 序列对象独立存在（`\ds` 查看），`currval`/`nextval` 可手动取号
- UUID vs 自增：分布式/无冲突插入选 UUID；单库自增更省空间且索引更友好——别无脑 UUID

## DDL 事务性：改表结构不用怕

```sql
BEGIN;
ALTER TABLE users ADD COLUMN nickname varchar(64);
-- 看到列名不合适？直接回滚，不用写一堆恢复脚本
ROLLBACK;
```

MySQL 转过来的第一惊喜：**DDL 也是事务的一部分**。配合先在测试库演练的习惯，改表结构的心理负担小很多。

> ⚠️ 但注意：加列是快的，**改列类型、加无默认值的唯一约束等操作会锁表重写**——大表 DDL 前先查该版本的锁行为（这属于"动手核对"点）。

## schema：一个库里的命名空间

```sql
CREATE SCHEMA pay;                        -- 业务分区内聚：public(通用) / shop / log
CREATE TABLE shop.orders (...);
SELECT * FROM shop.orders;                -- 跨 schema 用 点 访问
SET search_path TO shop, public;          -- 搜索路径决定不带前缀时先找哪个
```

用途：一个库服务多个模块/多租户隔离（每租户一个 schema）、扩展对象隔离（pgvector 的对象在 extension 的 schema 里）。

---
⬅️ [[01-PostgreSQL 基础与 MySQL 对照|01-PostgreSQL 基础与 MySQL 对照]] 🏠 [[00-数据库|00-数据库]] ➡️ [[03-PostgreSQL 查询进阶|03-PostgreSQL 查询进阶]]